"""
版本说明：当前文件主要增加了根据数据库参数表中math_open_lots 参数是否开启，来决定是否根据算法计算开仓手数的功能，
        也就是说，每次可以不止下单1手。而是根据资金量、保证金等条件来计算下单开仓量。
"""

import time, os, sqlite3
from threading import Thread, Lock
from dbutils.pooled_db import PooledDB
import numpy as np
import pandas as pd
from tqsdk import TqApi, TqAuth, TqKq
from rich.console import Console
from rich.theme import Theme
#from MyTT import REF, EMA, SUM
import logging
from algorithm_module import calculate_trading_signals,calculate_boeyi_indicators
#使用数据库连接池来管理数据库操作
# db = PooledDB(
#     creator=sqlite3,
#     database='trade_monitor.db',
#     maxconnections=5,
#     mincached=2,
#     maxcached=3,
#     maxshared=3,
#     blocking=True
# )

# 配置日志记录
logging.basicConfig(
    filename='trading_log.log',
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

# 改变控制台背景色和字体色，控制台窗口大小
# os.system("COLOR 02")
# os.system("mode con cols=150 lines=50")

# 定义颜色主题
# 定义更明亮显眼的颜色主题
custom_theme = Theme({
    "info": "bright_cyan",
    "warning": "bright_yellow",
    "error": "bright_red",
    "success": "bright_green",
    "trade": "bold bright_magenta"
})
console = Console(theme=custom_theme)

######################################## 定义全局变量########################################
symbols = []
api = None
threads = []

# 定义开仓资金比例（占可用资金的百分比）
POSITION_FUND_RATIO = 0.2  # 20%，可以根据需要调整
# 定义最大风险比例（已用资金占账户权益的最大比例）
MAX_RISK_RATIO = 0.2  # 20%，已用资金不能超过账户权益的20%
######################################## 定义函数########################################
# 新增函数：获取 config 表所有配置项
def get_all_configs():
    """
    从 config 表中获取所有配置项，以字典形式返回
    :return: 包含所有配置项的字典
    """
    try:
        # 每次查询都创建新的连接，避免线程安全问题
        conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
        cursor = conn.cursor()
        cursor.execute("SELECT key, value FROM config")
        results = cursor.fetchall()
        conn.close()
        return dict(results)
    except Exception as e:
        console.print(f"从数据库获取所有配置项时出错: {e}", style="error")
        return {}

def get_symbols():
    try:
        # 每次查询都创建新的连接，避免线程安全问题
        conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
        cursor = conn.cursor()
        # 查询品种代码和 mtp 字段
        cursor.execute("SELECT exchange_code FROM products WHERE is_monitored=1")
        products = cursor.fetchall()
        conn.close()
        # 返回包含品种代码和 mtp 值的列表
        return [product[0] for product in products]
    except Exception as e:
        console.print(f"从数据库获取监控品种时出错: {e}", style="error")
        return []

# 新增函数：记录交易到数据库
def insert_trade_record(symbol, direction, offset, volume, price, reason):
    try:
        # 每次查询都创建新的连接，避免线程安全问题
        conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO trade_records (product_name, direction, offset, volume, price, reason, timestamp) "
            "VALUES (?, ?, ?, ?, ?, ?, ?)",
            (symbol, direction, offset, volume, price, reason, time.strftime('%Y-%m-%d %X'))
        )
        conn.commit()
        conn.close()
    except Exception as e:
        console.print(f"记录交易到数据库时出错: {e}", style="error")

def check_position_and_orders(api, symbol):
    """
    检查指定品种是否有未完成订单或已有持仓
    :param api: TqSdk API对象
    :param symbol: 交易品种
    :return: (bool, str) - (是否可以开仓, 原因)
    """
    try:
        # 获取配置信息
        configs = get_all_configs()
        math_open_lots = configs.get('math_open_lots', 'off').lower() == 'on'
        
        # 检查持仓情况
        position = api.get_position(symbol)
        if not math_open_lots:
            if hasattr(position, 'pos_long') and position.pos_long > 0:
                return False, f"{symbol}已有{position.pos_long}手多仓持仓"
            if hasattr(position, 'pos_short') and position.pos_short > 0:
                return False, f"{symbol}已有{position.pos_short}手空仓持仓"
        
        # 检查未完成订单（仅检查开仓单）
        orders = api.get_order()
        if hasattr(orders, 'list') and callable(orders.list):
            order_list = orders.list()
        elif isinstance(orders, list):
            order_list = orders
        elif isinstance(orders, dict):
            order_list = list(orders.values())
        else:
            order_list = list(orders)
        
        for order in order_list:
            if hasattr(order, 'symbol') and hasattr(order, 'status') and hasattr(order, 'offset'):
                order_symbol = order.symbol
                order_status = order.status
                order_offset = order.offset
            elif hasattr(order, 'get'):
                order_symbol = order.get('symbol')
                order_status = order.get('status')
                order_offset = order.get('offset')
            else:
                order_symbol = order['symbol'] if 'symbol' in order else None
                order_status = order['status'] if 'status' in order else None
                order_offset = order['offset'] if 'offset' in order else None
            
            if (order_symbol == symbol and 
                order_status not in ('FINISHED', 'CANCELED') and 
                order_offset == 'OPEN'):
                return False, f"{symbol}有未完成开仓订单"
        
        return True, "无未完成订单，可以开仓"
    except Exception as e:
        logging.error(f"检查{symbol}持仓和订单时出错: {str(e)}", exc_info=True)
        return False, f"检查出错: {str(e)}"

def stop_loss(symbol):
    # 在每个线程中创建独立的数据库连接，而不是使用连接池
    api = TqApi(account=TqKq(), auth=TqAuth("cps168", "alibaba"))
    while True:
        deadline = time.time() + 2
        api.wait_update(deadline=deadline)

        #########################查询数据库中设置信息##########################################################
        try:
            # 每次查询都创建新的连接，避免线程安全问题
            conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
            cursor = conn.cursor()
            cursor.execute("SELECT limit_stoploss,mtp,dks FROM products WHERE exchange_code=? AND is_monitored=1", (symbol,))
            result = cursor.fetchone()
            conn.close()
            if result is None:
                stop_loss_limit = 3000  # 限额止损
                mtp = 5  # 移动止盈止损周期
                dks = "双向"  # 开仓方向
            else:
                stop_loss_limit = int(result[0])  # 限额止损
                # 处理 mtp 数据，确保其为有效的整数
                try:
                    mtp = int(result[1])
                    # 限制 mtp 在合理范围内，例如 1 到 3600
                    mtp = max(1, min(mtp, 3600))
                except (ValueError, TypeError):
                    console.print(f"从数据库获取 {symbol} 的 mtp 值无效，使用默认值", style="warning")
                    mtp = 5
                dks = str(result[2])  # 开仓方向
        except Exception as e:
            console.print(f"从数据库获取 {symbol} 止损限额时出错: {e}", style="error")
            return

        #########################获取数据逻辑#######################################################################
        # 获取 k 线数据
        try:
            klines = api.get_kline_serial(symbol, 60 * mtp, data_length=300)
            quote = api.get_quote(symbol)
            position = api.get_position(symbol)
            orders = api.get_order(symbol)
            console.print(f"{time.strftime('%X')} 开始处理 {symbol}", style="warning")
        except Exception as e:
            if "non-existent instrument" in str(e):
                console.print(f"获取 {symbol} 数据时出错，合约可能不存在，请检查合约代码: {e}", style="error")
            else:
                console.print(f"获取 {symbol} 数据时出错: {e}", style="error")
            return


        # 撤销所有未成交订单
        for order_id, order in orders.items():
            if hasattr(order, 'status') and order.status == 'ALIVE':
                api.cancel_order(order)

        console.print(f"{time.strftime('%X')} 开始检查{symbol}止损条件", style="warning")
        try:
        #########################处理各种类型的止损逻辑##########################################################
            if api.is_changing(quote):
                # ***************************************限额止损检查***************************************
                if position.pos_short > 0:
                    short_loss_per_hand = (position.open_price_short - quote.last_price) * quote.volume_multiple 
                    if short_loss_per_hand > stop_loss_limit:
                        if symbol.startswith('SHFE'):
                            if position.pos_short_today > 0:
                                limit_price = quote.ask_price1 + 2 * quote.price_tick
                                volume = position.pos_short_today
                                if volume > 0 and not np.isnan(limit_price):
                                    api.insert_order(
                                        symbol=symbol,
                                        direction="BUY",
                                        offset="CLOSETODAY",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 平{symbol}今空仓(限额止损): 手数: {volume}",
                                        style="trade"
                                    )
                                    # 仅保留下单信息日志
                                    logging.info(
                                        f"平{symbol}今空仓(限额止损): 手数: {volume}, 平仓价格: {limit_price}"
                                    )
                                    insert_trade_record(symbol, "BUY", "CLOSETODAY", volume, limit_price, "限额止损")
                            if position.pos_short_his > 0:
                                limit_price = quote.ask_price1 + 2 * quote.price_tick
                                volume = position.pos_short_his
                                if volume > 0 and not np.isnan(limit_price):
                                    api.insert_order(
                                        symbol=symbol,
                                        direction="BUY",
                                        offset="CLOSE",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 平{symbol}昨空仓(限额止损): 手数: {volume}",
                                        style="trade"
                                    )
                                    # 仅保留下单信息日志
                                    logging.info(
                                        f"平{symbol}昨空仓(限额止损): 手数: {volume}, 平仓价格: {limit_price}"
                                    )
                                    insert_trade_record(symbol, "BUY", "CLOSE", volume, limit_price, "限额止损")
                        else:
                            if position.pos_short > 0:
                                limit_price = quote.ask_price1 + 2 * quote.price_tick
                                volume = position.pos_short
                                if volume > 0 and not np.isnan(limit_price):
                                    api.insert_order(
                                        symbol=symbol,
                                        direction="CLOSE",
                                        offset="CLOSE",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 平{symbol}空仓(限额止损): 手数: {volume}",
                                        style="trade"
                                    )
                                    # 仅保留下单信息日志
                                    logging.info(
                                        f"平{symbol}空仓(限额止损): 手数: {volume}, 平仓价格: {limit_price}"
                                    )


                if position.pos_long > 0:
                    long_loss_per_hand = (quote.last_price - position.open_price_long) * quote.volume_multiple 
                    if long_loss_per_hand > stop_loss_limit:
                        if symbol.startswith('SHFE'):
                            if position.pos_long_today > 0:
                                limit_price = quote.bid_price1 - 2 * quote.price_tick
                                volume = position.pos_long_today
                                if volume > 0 and not np.isnan(limit_price):
                                    api.insert_order(
                                        symbol=symbol,
                                        direction="SELL",
                                        offset="CLOSETODAY",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 平{symbol}今多仓(均线追踪止损): 手数: {volume}",
                                        style="trade"
                                    )
                                    logging.info(
                                        f"平{symbol}今多仓(均线追踪止损): 手数: {volume}, 平仓价格: {limit_price}"
                                    )
                                    insert_trade_record(symbol, "SELL", "CLOSETODAY", volume, limit_price, "均线追踪止损")
                            if position.pos_long_his > 0:
                                limit_price = quote.bid_price1 - 2 * quote.price_tick
                                volume = position.pos_long_his
                                if volume > 0 and not np.isnan(limit_price):
                                    api.insert_order(
                                        symbol=symbol,
                                        direction="SELL",
                                        offset="CLOSE",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 平{symbol}昨多仓(均线追踪止损): 手数: {volume}",
                                        style="trade"
                                    )
                                    logging.info(
                                        f"平{symbol}昨多仓(均线追踪止损): 手数: {volume}, 平仓价格: {limit_price}"
                                    )
                                    insert_trade_record(symbol, "SELL", "CLOSE", volume, limit_price, "均线追踪止损")
                        else:
                            if position.pos_long > 0:
                                limit_price = quote.bid_price1 - 2 * quote.price_tick
                                volume = position.pos_long
                                if volume > 0 and not np.isnan(limit_price):
                                    api.insert_order(
                                        symbol=symbol,
                                        direction="SELL",
                                        offset="CLOSE",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 平{symbol}多仓(均线追踪止损): 手数: {volume}",
                                        style="trade"
                                    )
                                    logging.info(
                                        f"平{symbol}多仓(均线追踪止损): 手数: {volume}, 平仓价格: {limit_price}"
                                    )
                                    insert_trade_record(symbol, "SELL", "CLOSE", volume, limit_price, "均线追踪止损")

                # ***************************************趋势追踪止损检查***************************************
                #用新的算法计算操盘线和空头线的值
                try:
                    trading_line, short_line, signals = calculate_boeyi_indicators(klines)
                except Exception as e:
                    console.print(f"计算指标出错: {e}", style="error")
                    # 创建空的Series以避免后续代码出错
                    trading_line = pd.Series()
                    short_line = pd.Series()
                    signals = pd.Series()
                #print(trading_line)
                #print(short_line)
                #logging.info(f"Symbol: {symbol}, Trading Line[-2]: {trading_line[-2]}, Short Line[-2]: {short_line[-2]}")
                try:
                    if len(trading_line) >= 2 and len(short_line) >= 2 and not trading_line.empty and not short_line.empty:
                        console.print(f"条件值是： {trading_line.iloc[-2]}, {short_line.iloc[-2]}",style="trade")
                    else:
                        console.print(f"条件值不足，trading_line长度: {len(trading_line)}, short_line长度: {len(short_line)}", style="warning")
                except Exception as e:
                    console.print(f"获取条件值出错: {e}", style="error")
                    logging.error(f"获取条件值出错: {e}")
                
                if len(trading_line) >= 2 and len(short_line) >= 2 and not trading_line.empty and not short_line.empty:
                    # 处理空单
                    if position.pos_short > 0:
                        try:
                            # 确保 trading_line 和 short_line 有足够的数据点
                            if trading_line.iloc[-2] > short_line.iloc[-2]:
                                pass
                        except Exception as e:
                            console.print(f"检查空单止损条件时出错: {e}", style="error")
                            logging.error(f"检查空单止损条件时出错: {e}")
                            # 跳过止损
                            continue
                            
                        # 如果条件满足，继续执行止损逻辑
                        if len(trading_line) >= 2 and len(short_line) >= 2 and not trading_line.empty and not short_line.empty and trading_line.iloc[-2] > short_line.iloc[-2]:
                            if symbol.startswith('SHFE'):
                                if position.pos_short_today > 0:
                                    volume = position.pos_short_today
                                    limit_price = quote.ask_price1 + 2 * quote.price_tick                               
                                    if volume > 0 and not np.isnan(limit_price):
                                        api.insert_order(                        
                                                symbol=symbol,
                                                direction="BUY",
                                                offset="CLOSETODAY", 
                                                volume=volume,
                                                limit_price=limit_price
                                            )
                                        console.print(
                                            f"{time.strftime('%X')} 平{symbol}今空仓(均线追踪止损): 手数: {position.pos_short_today}",
                                            style="trade"
                                        )
                                        logging.info(
                                            f"平{symbol}今空仓(均线追踪止损): 手数: {position.pos_short_today}, 平仓价格: {limit_price}"
                                        )
                                        insert_trade_record(symbol, "BUY", "CLOSETODAY", position.pos_short_today, limit_price, "均线追踪止损")
                                if position.pos_short_his > 0:
                                    limit_price = quote.ask_price1 + 2 * quote.price_tick
                                    volume = position.pos_short_his
                                    if volume > 0 and not np.isnan(limit_price):
                                        api.insert_order(
                                            symbol=symbol,
                                            direction="BUY",
                                            offset="CLOSE",
                                            volume=volume,
                                            limit_price=limit_price
                                        )
                                        console.print(
                                            f"{time.strftime('%X')} 平{symbol}昨空仓(均线追踪止损): 手数: {position.pos_short_his}",
                                            style="trade"
                                        )
                                        logging.info(
                                            f"平{symbol}昨空仓(均线追踪止损): 手数: {position.pos_short_his}, 平仓价格: {limit_price}"
                                        )
                            else:
                                limit_price = quote.ask_price1 + 2 * quote.price_tick
                                volume = position.pos_short
                                if volume > 0 and not np.isnan(limit_price):
                                    api.insert_order(
                                        symbol=symbol,
                                        direction="BUY",
                                        offset="CLOSE",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 平{symbol}空仓(均线追踪止损): 手数: {position.pos_short}",
                                        style="trade"
                                    )
                                    logging.info(
                                        f"平{symbol}空仓(均线追踪止损): 手数: {position.pos_short}, 平仓价格: {limit_price}"
                                    )
                    # 处理多单#####################################################################
                    if position.pos_long > 0:
                        try:
                            # 确保 trading_line 和 short_line 有足够的数据点
                            if trading_line.iloc[-2] < short_line.iloc[-2]:
                                pass
                        except Exception as e:
                            console.print(f"检查多单止损条件时出错: {e}", style="error")
                            logging.error(f"检查多单止损条件时出错: {e}")
                            # 跳过止损
                            continue
                            
                        # 如果条件满足，继续执行止损逻辑
                        if len(trading_line) >= 2 and len(short_line) >= 2 and not trading_line.empty and not short_line.empty and trading_line.iloc[-2] < short_line.iloc[-2]:
                            #上海交易所多单止损
                            if symbol.startswith('SHFE'):
                                if position.pos_long_his > 0:
                                    limit_price = quote.bid_price1 - 2 * quote.price_tick
                                    volume = position.pos_long_his
                                    if volume > 0 and not np.isnan(limit_price):
                                        api.insert_order(
                                            symbol=symbol,
                                            direction="SELL",
                                            offset="CLOSE",
                                            volume=volume,
                                            limit_price=limit_price
                                        )
                                        console.print(
                                            f"{time.strftime('%X')} 平{symbol}昨多仓(均线追踪止损): 手数: {volume}",
                                            style="trade"
                                        )
                                        logging.info(
                                            f"平{symbol}昨多仓(均线追踪止损): 手数: {volume}, 平仓价格: {limit_price}"
                                        )
                                        insert_trade_record(symbol, "SELL", "CLOSE", volume, limit_price, "均线追踪止损")
                                if position.pos_long_today > 0:
                                    limit_price = quote.bid_price1 - 2 * quote.price_tick
                                    volume = position.pos_long_today
                                    if volume > 0 and not np.isnan(limit_price):
                                        api.insert_order(
                                            symbol=symbol,
                                            direction="SELL",
                                            offset="CLOSETODAY",
                                            volume=volume,
                                            limit_price=limit_price
                                        )
                                        console.print(
                                            f"{time.strftime('%X')} 平{symbol}今多仓(均线追踪止损): 手数: {volume}",
                                            style="trade"
                                        )
                                        logging.info(
                                            f"平{symbol}今多仓(均线追踪止损): 手数: {volume}, 平仓价格: {limit_price}"
                                        )
                                        insert_trade_record(symbol, "SELL", "CLOSETODAY", volume, limit_price, "均线追踪止损")
                            else:
                                #非上海交易所多单追踪止损        
                                limit_price = quote.bid_price1 - 2 * quote.price_tick
                                volume = position.pos_long
                                if volume > 0 and not np.isnan(limit_price):
                                    api.insert_order(
                                        symbol=symbol,
                                        direction="SELL",
                                        offset="CLOSE",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 平{symbol}多仓(均线追踪止损): 手数: {volume}",
                                        style="trade"
                                    )
                                    logging.info(
                                        f"平{symbol}多仓(均线追踪止损): 手数: {volume}, 平仓价格: {limit_price}"
                                    )
                                    insert_trade_record(symbol, "SELL", "CLOSE", volume, limit_price, "均线追踪止损")
        except Exception as e:
            console.print(f"处理 {symbol} 止损逻辑时出错: {e}", style="error")

        #########################处理开仓逻辑####################################################################
        console.print(f"{time.strftime('%X')} 开始检查{symbol}开仓条件", style="warning")
        try:
            # 获取所有配置项
            configs = get_all_configs()
            open_auto = configs.get('open_auto', 'off').lower() == 'on'
            can_open, reason = check_position_and_orders(api, symbol) #检查当前品种是否有持仓或者未成交委托单

            if open_auto:
                #出现新K线，或者行情发生变化时
                if can_open: 
                    #新出K线时
                    if  api.is_changing(klines.iloc[-1], 'datetime'):
                        # 获取配置信息
                        configs = get_all_configs()
                        math_open_lots = configs.get('math_open_lots', 'off').lower() == 'on'
                        
                        # 检查是否需要开多仓
                        try:
                            # 确保 trading_line 和 short_line 有足够的数据点
                            if (len(trading_line) >= 3 and len(short_line) >= 3 and 
                                not trading_line.empty and not short_line.empty and
                                (math_open_lots or position.pos_long==0) and 
                                (dks == "只做多" or dks == "双向") and 
                                trading_line.iloc[-2] > short_line.iloc[-2] and 
                                trading_line.iloc[-3] < short_line.iloc[-3]):
                                pass
                        except Exception as e:
                            console.print(f"检查开多仓条件时出错: {e}", style="error")
                            logging.error(f"检查开多仓条件时出错: {e}")
                            # 跳过开仓
                            continue
                            
                        # 如果条件满足，继续执行开仓逻辑
                        if (len(trading_line) >= 3 and len(short_line) >= 3 and 
                            not trading_line.empty and not short_line.empty and
                            (math_open_lots or position.pos_long==0) and 
                            (dks == "只做多" or dks == "双向") and 
                            trading_line.iloc[-2] > short_line.iloc[-2] and 
                            trading_line.iloc[-3] < short_line.iloc[-3]):

                        # 对上海交易所的商品开多仓
                            if symbol.startswith('SHFE'):
                                limit_price = quote.ask_price1 + 2 * quote.price_tick
                                # 开仓并获取订单ID
                                # 查询数据库中config表的math_open_lots字段
                                math_open_lots = configs.get('math_open_lots', 'off').lower() == 'on'
                                if math_open_lots:
                                    # 从数据库的products表获取当前品种每手保证金
                                    conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
                                    cursor = conn.cursor()
                                    cursor.execute('SELECT margin FROM products WHERE exchange_code = ?', (symbol,))
                                    product_bond = cursor.fetchone()
                                    conn.close()
                                    if product_bond and product_bond[0]:
                                        # 步骤1: 获取账户完整信息
                                        account = api.get_account()
                                        available_funds = account.available  # 可用资金
                                        balance = account.balance  # 账户权益(总资产)
                                        used_margin = account.margin  # 已用保证金
                                        
                                        # 步骤2: 检查风险比例 - 已用保证金不能超过账户权益的MAX_RISK_RATIO(20%)
                                        if used_margin / balance > MAX_RISK_RATIO:
                                            console.print(f"已用保证金({used_margin})超过账户权益({balance})的{MAX_RISK_RATIO*100}%，不再开仓", style="warning")
                                            volume = 0  # 风险过高，不开仓
                                        else:
                                            # 步骤3: 计算还可以使用的保证金额度
                                            remaining_margin = balance * MAX_RISK_RATIO - used_margin
                                            
                                            # 步骤4: 计算实际可用于开仓的资金
                                            # 取两个限制条件的较小值: 
                                            # 1. 可用资金的POSITION_FUND_RATIO(20%)
                                            # 2. 剩余可用保证金额度
                                            usable_funds = min(available_funds * POSITION_FUND_RATIO, remaining_margin)
                                            
                                            # 步骤5: 计算理论目标持仓量
                                            # 可用资金除以每手保证金，再除以3(分散风险)
                                            target_volume = int((usable_funds / product_bond[0]) / 3)
                                            
                                            # 步骤6: 获取已有持仓量
                                            current_position = position.pos_long  # 当前多仓持仓量
                                            
                                            # 步骤7: 计算实际需要开仓的手数
                                            # 目标持仓量减去已有持仓量，最小为0(不开仓)
                                            volume = max(0, target_volume - current_position)
                                    else:
                                        volume = 1
                                #print("当前品种开仓手数是：",volume)
                                # 执行开仓操作
                                if volume > 0 and not np.isnan(limit_price):
                                    order_id = api.insert_order(
                                        symbol=symbol,
                                        direction="BUY",
                                        offset="OPEN",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 开{symbol}多仓: 手数: {volume}, 订单ID: {order_id}",
                                        style="trade"
                                    )
                                    logging.info(
                                        f"开{symbol}多仓: 手数: {volume}, 开仓价格: {limit_price}, 订单ID: {order_id}"
                                    )
                                else:
                                    console.print(
                                        f"{time.strftime('%X')} 跳过{symbol}开多仓: 手数为0（风险控制或无需开仓）",
                                        style="warning"
                                    )
                                    logging.info(f"跳过{symbol}开多仓: 手数为0（风险控制或无需开仓）")

                                # 检查订单是否在0.5秒内成交
                                deadline = time.time() + 0.5
                                order_filled = False
                                order = api.get_order(order_id)
                                api.wait_update(deadline=deadline)
                                if order:
                                    if api.is_changing(order, 'status') and order.status == 'FINISHED':
                                        order_filled = True
                                        console.print(
                                            f"{time.strftime('%X')} {symbol}多仓订单已成交",
                                            style="success"
                                        )
                                        logging.info(f"{symbol}多仓订单已成交: {order_id}")
                                        insert_trade_record(symbol, "BUY", "OPEN", volume, limit_price, "开多仓")

                                # 如果未成交，撤销订单
                                if not order_filled:
                                    api.cancel_order(order_id)
                                    console.print(
                                        f"{time.strftime('%X')} {symbol}多仓订单0.5秒未成交，已撤销",
                                        style="warning"
                                    )
                                    logging.info(f"{symbol}多仓订单0.5秒未成交，已撤销: {order_id}")
                            # 非上海交易所的商品开多仓
                            else:
                                limit_price = quote.ask_price1 + 2 * quote.price_tick
                                # 查询数据库中config表的math_open_lots字段
                                math_open_lots = configs.get('math_open_lots', 'off').lower() == 'on'
                                if math_open_lots:
                                    # 从数据库的products表获取当前品种每手保证金
                                    conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
                                    cursor = conn.cursor()
                                    cursor.execute('SELECT margin FROM products WHERE exchange_code = ?', (symbol,))
                                    product_bond = cursor.fetchone()
                                    conn.close()
                                    if product_bond and product_bond[0]:
                                        # 步骤1: 获取账户完整信息
                                        account = api.get_account()
                                        available_funds = account.available  # 可用资金
                                        balance = account.balance  # 账户权益(总资产)
                                        used_margin = account.margin  # 已用保证金
                                        
                                        # 步骤2: 检查风险比例 - 已用保证金不能超过账户权益的MAX_RISK_RATIO(20%)
                                        if used_margin / balance > MAX_RISK_RATIO:
                                            console.print(f"已用保证金({used_margin})超过账户权益({balance})的{MAX_RISK_RATIO*100}%，不再开仓", style="warning")
                                            volume = 0  # 风险过高，不开仓
                                        else:
                                            # 步骤3: 计算还可以使用的保证金额度
                                            remaining_margin = balance * MAX_RISK_RATIO - used_margin
                                            
                                            # 步骤4: 计算实际可用于开仓的资金
                                            # 取两个限制条件的较小值: 
                                            # 1. 可用资金的POSITION_FUND_RATIO(20%)
                                            # 2. 剩余可用保证金额度
                                            usable_funds = min(available_funds * POSITION_FUND_RATIO, remaining_margin)
                                            
                                            # 步骤5: 计算理论目标持仓量
                                            # 可用资金除以每手保证金，再除以3(分散风险)
                                            target_volume = int((usable_funds / product_bond[0]) / 3)
                                            
                                            # 步骤6: 获取已有持仓量
                                            current_position = position.pos_long  # 当前多仓持仓量
                                            
                                            # 步骤7: 计算实际需要开仓的手数
                                            # 目标持仓量减去已有持仓量，最小为0(不开仓)
                                            volume = max(0, target_volume - current_position)
                                    else:
                                        volume = 1
                                if volume > 0 and not np.isnan(limit_price):
                                    # 开仓并获取订单ID
                                    order_id = api.insert_order(
                                        symbol=symbol,
                                        direction="BUY",
                                        offset="OPEN",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 开{symbol}多仓: 手数: {volume}, 订单ID: {order_id}",
                                        style="trade"
                                    )
                                    logging.info(
                                        f"开{symbol}多仓: 手数: {volume}, 开仓价格: {limit_price}, 订单ID: {order_id}"
                                    )
                                    # 检查订单是否在0.5秒内成交
                                    deadline = time.time() + 0.5
                                    order_filled = False
                                    order = api.get_order(order_id)
                                    api.wait_update(deadline=deadline)
                                    if order:
                                        if api.is_changing(order, 'status') and order.status == 'FINISHED':
                                            order_filled = True
                                            console.print(
                                                f"{time.strftime('%X')} {symbol}多仓订单已成交",
                                                style="success"
                                            )
                                            logging.info(f"{symbol}多仓订单已成交: {order_id}")
                                            insert_trade_record(symbol, "BUY", "OPEN", volume, limit_price, "开多仓")
                                        else:
                                            console.print(
                                                f"{time.strftime('%X')} {symbol}多仓订单未成交",
                                                style="warning"
                                            )
                                            logging.info(f"{symbol}多仓订单未成交: {order_id}")
                                    # 如果未成交，撤销订单
                                    if not order_filled:
                                        api.cancel_order(order_id)
                                        console.print(
                                            f"{time.strftime('%X')} {symbol}多仓订单0.5秒未成交，已撤销",
                                            style="warning"
                                        )
                                        logging.info(f"{symbol}多仓订单0.5秒未成交，已撤销: {order_id}")
                                else:
                                    console.print(
                                        f"{time.strftime('%X')} 跳过{symbol}开多仓: 手数为0（风险控制或无需开仓）",
                                        style="warning"
                                    )
                                    logging.info(f"跳过{symbol}开多仓: 手数为0（风险控制或无需开仓）")


                        # 检查是否需要开空仓
                        try:
                            # 确保 trading_line 和 short_line 有足够的数据点
                            if (len(trading_line) >= 3 and len(short_line) >= 3 and 
                                not trading_line.empty and not short_line.empty and
                                (math_open_lots or position.pos_short == 0) and 
                                (dks == "只做空" or dks == "双向") and 
                                trading_line.iloc[-2] < short_line.iloc[-2] and 
                                trading_line.iloc[-3] > short_line.iloc[-3]):
                                pass
                        except Exception as e:
                            console.print(f"检查开空仓条件时出错: {e}", style="error")
                            logging.error(f"检查开空仓条件时出错: {e}")
                            # 跳过开仓
                            continue
                            
                        # 如果条件满足，继续执行开仓逻辑
                        if (len(trading_line) >= 3 and len(short_line) >= 3 and 
                            not trading_line.empty and not short_line.empty and
                            (math_open_lots or position.pos_short == 0) and 
                            (dks == "只做空" or dks == "双向") and 
                            trading_line.iloc[-2] < short_line.iloc[-2] and 
                            trading_line.iloc[-3] > short_line.iloc[-3]):
                            # 对上海交易所的商品开空仓
                            if symbol.startswith('SHFE'):
                                limit_price = quote.bid_price1 - 2 * quote.price_tick
                                # 查询数据库中config表的math_open_lots字段
                                math_open_lots = configs.get('math_open_lots', 'off').lower() == 'on'
                                if math_open_lots:
                                    # 从数据库的products表获取当前品种每手保证金
                                    conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
                                    cursor = conn.cursor()
                                    cursor.execute('SELECT margin FROM products WHERE exchange_code = ?', (symbol,))
                                    product_bond = cursor.fetchone()
                                    conn.close()
                                    if product_bond and product_bond[0]:
                                        # 步骤1: 获取账户完整信息
                                        account = api.get_account()
                                        available_funds = account.available  # 可用资金
                                        balance = account.balance  # 账户权益(总资产)
                                        used_margin = account.margin  # 已用保证金
                                        
                                        # 步骤2: 检查风险比例 - 已用保证金不能超过账户权益的MAX_RISK_RATIO(20%)
                                        if used_margin / balance > MAX_RISK_RATIO:
                                            console.print(f"已用保证金({used_margin})超过账户权益({balance})的{MAX_RISK_RATIO*100}%，不再开仓", style="warning")
                                            volume = 0  # 风险过高，不开仓
                                        else:
                                            # 步骤3: 计算还可以使用的保证金额度
                                            remaining_margin = balance * MAX_RISK_RATIO - used_margin
                                            
                                            # 步骤4: 计算实际可用于开仓的资金
                                            # 取两个限制条件的较小值: 
                                            # 1. 可用资金的POSITION_FUND_RATIO(20%)
                                            # 2. 剩余可用保证金额度
                                            usable_funds = min(available_funds * POSITION_FUND_RATIO, remaining_margin)
                                            
                                            # 步骤5: 计算理论目标持仓量
                                            # 可用资金除以每手保证金，再除以3(分散风险)
                                            target_volume = int((usable_funds / product_bond[0]) / 3)
                                            
                                            # 步骤6: 获取已有持仓量
                                            current_position = position.pos_short  # 当前空仓持仓量
                                            
                                            # 步骤7: 计算实际需要开仓的手数
                                            # 目标持仓量减去已有持仓量，最小为0(不开仓)
                                            volume = max(0, target_volume - current_position)
                                    else:
                                        volume = 1
                                # 开仓并获取订单ID
                                if volume > 0 and not np.isnan(limit_price):
                                    order_id = api.insert_order(
                                        symbol=symbol,
                                        direction="SELL",
                                        offset="OPEN",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 开{symbol}空仓: 手数: {volume}, 订单ID: {order_id}",
                                        style="trade"
                                    )
                                    logging.info(
                                        f"开{symbol}空仓: 手数: {volume}, 开仓价格: {limit_price}, 订单ID: {order_id}"
                                    )
                                else:
                                    console.print(
                                        f"{time.strftime('%X')} 跳过{symbol}开空仓: 手数为0（风险控制或无需开仓）",
                                        style="warning"
                                    )
                                    logging.info(f"跳过{symbol}开空仓: 手数为0（风险控制或无需开仓）")
                            # 检查订单是否在0.5秒内成交
                            deadline = time.time() + 0.5
                            order_filled = False
                            order = api.get_order(order_id)
                            api.wait_update(deadline=deadline)
                            if order:
                                if api.is_changing(order, 'status') and order.status == 'FINISHED':
                                    order_filled = True
                                    console.print(
                                        f"{time.strftime('%X')} {symbol}空仓订单已成交",
                                        style="success"
                                    )
                                    logging.info(f"{symbol}空仓订单已成交: {order_id}")
                                    insert_trade_record(symbol, "SELL", "OPEN", volume, limit_price, "开空仓")
                                else:
                                    console.print(
                                        f"{time.strftime('%X')} {symbol}空仓订单未成交",
                                        style="warning"
                                    )
                                    logging.info(f"{symbol}空仓订单未成交: {order_id}")
                                # 如果未成交，撤销订单
                                if not order_filled:
                                    api.cancel_order(order_id)
                                    console.print(
                                        f"{time.strftime('%X')} {symbol}空仓订单0.5秒未成交，已撤销",
                                        style="warning"
                                    )
                                    logging.info(f"{symbol}空仓订单0.5秒未成交，已撤销: {order_id}")
                            # 非上海交易所的商品开空仓
                            else:
                                limit_price = quote.bid_price1 - 2 * quote.price_tick
                                # 查询数据库中config表的math_open_lots字段
                                math_open_lots = configs.get('math_open_lots', 'off').lower() == 'on'
                                #计算下单量
                                if math_open_lots:
                                    # 从数据库的products表获取当前品种每手保证金
                                    conn = sqlite3.connect('trade_monitor.db', check_same_thread=False)
                                    cursor = conn.cursor()
                                    cursor.execute('SELECT margin FROM products WHERE exchange_code = ?', (symbol,))
                                    product_bond = cursor.fetchone()
                                    conn.close()
                                    if product_bond and product_bond[0]:
                                        # 步骤1: 获取账户完整信息
                                        account = api.get_account()
                                        available_funds = account.available  # 可用资金
                                        balance = account.balance  # 账户权益(总资产)
                                        used_margin = account.margin  # 已用保证金
                                        
                                        # 步骤2: 检查风险比例 - 已用保证金不能超过账户权益的MAX_RISK_RATIO(20%)
                                        if used_margin / balance > MAX_RISK_RATIO:
                                            console.print(f"已用保证金({used_margin})超过账户权益({balance})的{MAX_RISK_RATIO*100}%，不再开仓", style="warning")
                                            volume = 0  # 风险过高，不开仓
                                        else:
                                            # 步骤3: 计算还可以使用的保证金额度
                                            remaining_margin = balance * MAX_RISK_RATIO - used_margin
                                            
                                            # 步骤4: 计算实际可用于开仓的资金
                                            # 取两个限制条件的较小值: 
                                            # 1. 可用资金的POSITION_FUND_RATIO(20%)
                                            # 2. 剩余可用保证金额度
                                            usable_funds = min(available_funds * POSITION_FUND_RATIO, remaining_margin)
                                            
                                            # 步骤5: 计算理论目标持仓量
                                            # 可用资金除以每手保证金，再除以3(分散风险)
                                            target_volume = int((usable_funds / product_bond[0]) / 3)
                                            
                                            # 步骤6: 获取已有持仓量
                                            current_position = position.pos_short  # 当前空仓持仓量
                                            
                                            # 步骤7: 计算实际需要开仓的手数
                                            # 目标持仓量减去已有持仓量，最小为0(不开仓)
                                            volume = max(0, target_volume - current_position)
                                    else:
                                        volume = 1

                                # 开仓并获取订单ID
                                if volume > 0 and not np.isnan(limit_price):
                                    order_id = api.insert_order(
                                        symbol=symbol,
                                        direction="SELL",
                                        offset="OPEN",
                                        volume=volume,
                                        limit_price=limit_price
                                    )
                                    console.print(
                                        f"{time.strftime('%X')} 开{symbol}空仓: 手数: {volume}, 订单ID: {order_id}",
                                        style="trade"
                                    )
                                    logging.info(
                                        f"开{symbol}空仓: 手数: {volume}, 开仓价格: {limit_price}, 订单ID: {order_id}"
                                    )
                                else:
                                    console.print(
                                        f"{time.strftime('%X')} 跳过{symbol}开空仓: 手数为0（风险控制或无需开仓）",
                                        style="warning"
                                    )
                                    logging.info(f"跳过{symbol}开空仓: 手数为0（风险控制或无需开仓）")

                                # 检查订单是否在0.5秒内成交
                                deadline = time.time() + 0.5
                                order_filled = False
                                order = api.get_order(order_id)
                                api.wait_update(deadline=deadline)
                                if order:
                                    if api.is_changing(order, 'status') and order.status == 'FINISHED':
                                        order_filled = True
                                        console.print(
                                            f"{time.strftime('%X')} {symbol}空仓订单已成交",
                                            style="success"
                                        )
                                        logging.info(f"{symbol}空仓订单已成交: {order_id}")
                                        insert_trade_record(symbol, "SELL", "OPEN", volume, limit_price, "开空仓")
                                    else:
                                        console.print(
                                            f"{time.strftime('%X')} {symbol}空仓订单未成交",
                                            style="warning"
                                        )
                                        logging.info(f"{symbol}空仓订单未成交: {order_id}")

                                # 如果未成交，撤销订单
                                if not order_filled:
                                    api.cancel_order(order_id)
                                    console.print(
                                        f"{time.strftime('%X')} {symbol}空仓订单0.5秒未成交，已撤销",
                                        style="warning"
                                    )
                                    logging.info(f"{symbol}空仓订单0.5秒未成交，已撤销: {order_id}")

                else:
                    console.print(f"{time.strftime('%X')} {symbol} 不可以开仓: {reason}", style="warning")
            else:
                console.print(f"{time.strftime('%X')} 自动开仓功能已关闭，跳过开仓检查", style="warning")
        except Exception as e:
            console.print(f"处理 {symbol} 开仓逻辑时出错: {e}", style="error")
            logging.error(f"处理 {symbol} 开仓逻辑时出错: {e}")
            # 记录详细的错误信息以便调试
            import traceback
            logging.error(traceback.format_exc())


##### ##############################################主函数##############################################
if __name__ == "__main__":
    threads = []
    try:
        # 获取待监控的品种
        symbol_mtp_list = get_symbols()
        console.print(f"**********{time.strftime('%X')} 系统开始运行 **********", style="warning")
        i=1
        for symbol in symbol_mtp_list:
            console.print(f"**********{time.strftime('%X')} {i} ，开始监控 {symbol} **********", style="warning")
            t = Thread(target=stop_loss, args=(symbol,), daemon=True)
            t.start()
            threads.append(t)
            time.sleep(3)
            i=i+1
        # 主线程等待所有子线程完成
        while True:
            time.sleep(10)
    except KeyboardInterrupt:
        console.print("正在停止所有线程...", style="warning")
        if api is not None:
            api.close()
    finally:
        console.print("程序已停止", style="info")
